#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--已报名用户事实表
INSERT INTO edu_online_dwd.fac_customer_signed
SELECT
    id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    first_id,
    belonger,
    belonger_name,
    initial_belonger,
    distribution_handler,
    business_scrm_department_id,
    last_visit_time,
    next_visit_time,
    origin_type,
    itcast_school_id,
    itcast_subject_id,
    intention_study_type,
    creator,
    current_creator,
    creator_name,
    origin_channel,
    first_customer_clue_id,
    last_customer_clue_id,
    process_state,
    process_time,
    payment_state,
    payment_time,
    signup_state,
    signup_time,
    notice_state,
    notice_time,
    lock_state,
    lock_time,
    itcast_clazz_id,
    itcast_clazz_time,
    payment_url,
    payment_url_time,
    ems_student_id,
    delete_reason,
    deleter,
    deleter_name,
    delete_time,
    course_id,
    course_name,
    delete_comment,
    close_state,
    close_time,
    total_fee,
    belonged,
    belonged_time,
    belonger_time,
    transfer,
    transfer_time,
    dt
FROM edu_online_ods.customer_relationship;

--来源渠道事实表
INSERT INTO edu_online_dwd.fac_source_channel
SELECT
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    customer_relationship_id,
    session_id,
    sid,
    status,
    \`user\`,
    create_time,
    platform,
    s_name,
    seo_source,
    seo_keywords,
    ip,
    referrer,
    from_url,
    landing_page_url,
    url_title,
    to_peer,
    manual_time,
    begin_time,
    reply_msg_count,
    total_msg_count,
    msg_count,
    comment,
    finish_reason,
    finish_user,
    end_time,
    platform_description,
    browser_name,
    os_info,
    area,
    country,
    province,
    city,
    creator,
    name,
    idcard,
    phone,
    itcast_school_id,
    itcast_school,
    itcast_subject_id,
    itcast_subject,
    wechat,
    qq,
    email,
    gender,
    level,
    origin_type,
    information_way,
    working_years,
    technical_directions,
    customer_state,
    valid,
    anticipat_signup_date,
    clue_state,
    scrm_department_id,
    superior_url,
    superior_source,
    landing_url,
    landing_source,
    info_url,
    info_source,
    is_repeat,
    follow_type,
    shunt_mode_id,
    shunt_employee_group_id,
    dt
FROM edu_online_ods.customer_clue;

--咨询中心事实表
INSERT INTO edu_online_dwd.fac_consulting_center
SELECT
    employee.id AS emp_id,
    employee.real_name AS emp_real_name,
    scrm_department.id,
    scrm_department.name,
    scrm_department.parent_id,
    scrm_department.create_date_time,
    scrm_department.update_date_time,
    scrm_department.deleted,
    scrm_department.id_path,
    scrm_department.tdepart_code,
    scrm_department.creator,
    scrm_department.depart_level,
    scrm_department.depart_sign,
    scrm_department.depart_line,
    scrm_department.depart_sort
FROM edu_online_ods.employee LEFT JOIN edu_online_ods.scrm_department
ON employee.scrm_department_id = scrm_department.id;

--有效线索个数
--数据问题，只采集有效线索个数作为计算使用
INSERT INTO edu_online_dwd.fac_valid_clue
SELECT
count(*) AS \`valid_clue_count\`
FROM edu_online_ods.customer_appeal
WHERE appeal_status = 2;

--校区学科维度表
INSERT INTO edu_online_dwd.dim_campus_subject
SELECT
    id,
    create_date_time,
    update_date_time,
    deleted,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    itcast_brand,
    clazz_type_state,
    clazz_type_name,
    teaching_mode,
    start_time,
    end_time,
    comment,
    detail,
    uncertain,
    dt
FROM edu_online_ods.itcast_clazz;"
